package br.com.Dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import br.com.Conexao.conexao;
import br.com.Modelo.Cupom;

public class CupomDao {
	
Connection connection;
	
	public CupomDao(){
		
		this.connection = new conexao().getConnection();
		
	}
	
	
	public void adiciona(Cupom cup) {
    	
    	//if(isLoginEqual(usuario)==false){
	    String sql = "insert into cupom_cliente " +
	            "(id_cupomCliente,progresso,mes_cupom,situacao,dia_cupom)" +
	            " values (?,?,?,?,?)";

	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

	        stmt.setInt(1,cup.getId_c_cli());
	        stmt.setInt(2,cup.getProgresso());
	        
	        Calendar c = Calendar.getInstance();
	        Calendar c2 = Calendar.getInstance();
	        
	        cup.setMes_cup(c);
	    
	    	java.sql.Date newDate = new java.sql.Date(cup.getMes_cup().getTimeInMillis());  
	        stmt.setDate(3,newDate);
	        
	        
	        cup.setDia_cup(c2);
	    	cup.getDia_cup().set(2014, 10, 12);		
			java.sql.Date newDateDia = new java.sql.Date(cup.getDia_cup().getTimeInMillis()); 
	             
	      
	        stmt.setString(4,cup.getSituacao());
	        stmt.setDate(5,newDateDia);
	       
	     

	        // executa
	        stmt.execute();
	        System.out.print("Pagina add com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }
    
	}
	
	public List<Cupom> getCupom(int id_cliente) {
		try {	
		List<Cupom> cups = new ArrayList<Cupom>();
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from cupom_cliente where id_cupomCliente='"+id_cliente+"'");
		ResultSet rs= stmt.executeQuery();
		
			while(rs.next()){
			    Cupom cup = new Cupom();
			    cup.setProgresso(rs.getInt("progresso"));
			    cup.setId_Cupom(rs.getInt("id_cupom"));
			    cup.setId_c_cli(rs.getInt("id_cupomCliente"));
			    cup.setId_c_com(rs.getInt("id_cupomComerciante"));
			    
			    // montando a data através do Calendar
			    Calendar data = Calendar.getInstance();
			    data.setTime(rs.getDate("mes_cupom"));
			    cup.setMes_cup(data);
			
		
			    
			      cups.add(cup);
		        }
			rs.close();
			stmt.close();
			//connection.close();
		     return cups;
		 } catch (SQLException e) {
		         throw new RuntimeException(e);
		  }
	    }
	
	public List<Cupom> getCupomCompleto(long id_cliente) {
		try {	
		List<Cupom> cups = new ArrayList<Cupom>();
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from cupom_cliente where id_cupomCliente='"+id_cliente+"' and progresso=100");
		ResultSet rs= stmt.executeQuery();
		
			while(rs.next()){
			    Cupom cup = new Cupom();
			    cup.setProgresso(rs.getInt("progresso"));
			    cup.setId_Cupom(rs.getInt("id_cupom"));
			    cup.setId_c_cli(rs.getInt("id_cupomCliente"));
			    cup.setId_c_com(rs.getInt("id_cupomComerciante"));
			    
			    // montando a data através do Calendar
			    Calendar data = Calendar.getInstance();
			    data.setTime(rs.getDate("mes_cupom"));
			    cup.setMes_cup(data);
			
		
			    
			      cups.add(cup);
		        }
			rs.close();
			stmt.close();
			//connection.close();
		     return cups;
		 } catch (SQLException e) {
		         throw new RuntimeException(e);
		  }
	    }

public int qtd_Cupons(int id_cliente){
try{
	PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("Select count(*) as qtd from cupom_cliente where progresso=100 and id_cupomCliente='"+id_cliente+"'");
	ResultSet rs= stmt.executeQuery();
	
	while(rs.next())
	    return rs.getInt("qtd");
	
	rs.close();
	stmt.close();
	connection.close();
	return 0;
 } catch (SQLException e) {
         throw new RuntimeException(e);
  }
}

public Cupom getCupomInativo(long id_cliente){
	try {	
		List<Cupom> cups = new ArrayList<Cupom>();
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from cupom_cliente where id_cupomCliente='"+id_cliente+"' and situacao='inativo' limit 1");
		ResultSet rs= stmt.executeQuery();
		
			while(rs.next()){
			    Cupom cup = new Cupom();
			    cup.setProgresso(rs.getInt("progresso"));
			    cup.setId_Cupom(rs.getInt("id_cupom"));
			    cup.setId_c_cli(rs.getInt("id_cupomCliente"));
			    cup.setId_c_com(rs.getInt("id_cupomComerciante"));
			    
			    // montando a data através do Calendar
			    Calendar data = Calendar.getInstance();
			    data.setTime(rs.getDate("mes_cupom"));
			    cup.setMes_cup(data);
			
			     return cup;
		
			   
		        }
			rs.close();
			stmt.close();
			//connection.close();
		    
		 } catch (SQLException e) {
		         throw new RuntimeException(e);
		  }
	return null;
}




public int verficarMesCupom(Long id_cliente, int mesAtual) {
	try {	
	List<Cupom> cups = new ArrayList<Cupom>();
	PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select mes_cupom from cupom_cliente where id_cupomCliente='"+id_cliente+"' and situacao='ativo' ");
	ResultSet rs= stmt.executeQuery();
	
		while(rs.next()){

			Cupom cp = getCupomInativo(id_cliente);
			
			int dataIna = cp.getMes_cup().get(Calendar.DAY_OF_MONTH);
		    // montando a data através do Calendar
		    Calendar data = Calendar.getInstance();
		    data.setTime(rs.getDate("mes_cupom"));	
		   // System.out.println("mes cupom sem 30 ->"+data.get(Calendar.DAY_OF_MONTH));
		   // data.add(Calendar.DAY_OF_MONTH, 30);
	         System.out.println("mes cupom  ->"+dataIna+"=="+mesAtual);
		    if(mesAtual - dataIna >= 30)
		    {
		    	 System.out.println("retorna 1");
		    	return 1;
		    	
		    }
	
		    
	        }
		rs.close();
		stmt.close();
		//connection.close();
	     return -1;
	 } catch (SQLException e) {
	         throw new RuntimeException(e);
	  }
    }



public void atualizarMesCupom(Long id_cliente,Calendar c) {
	
	  int aux=0;
	  
	  String sql = "UPDATE cupom_cliente SET mes_cupom=?, progresso=?, id_cupomComerciante=? , dia_cupom=? , situacao=? WHERE id_cupomCliente=?";
		
	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores
	        

	       stmt.setDate(1, new java.sql.Date(c.getTimeInMillis()));
	       stmt.setInt(2, 0);
	       stmt.setInt(3, -1); 
	       stmt.setDate(4, new java.sql.Date(c.getTimeInMillis())); 
	       stmt.setString(5, "inativo");
	       stmt.setLong(6, id_cliente); 
	     
	        // executa
	        stmt.executeUpdate();
	        System.out.print("update de mes com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }

	
    }

public int verficarDiaCupom(Long id_cliente, int mesAtual) {
	try {	
	List<Cupom> cups = new ArrayList<Cupom>();
	PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select dia_cupom from cupom_cliente where id_cupomCliente='"+id_cliente+"' and situacao='ativo' ");
	ResultSet rs= stmt.executeQuery();
	
		while(rs.next()){

		    
		    // montando a data através do Calendar
		    Calendar data = Calendar.getInstance();
		    data.setTime(rs.getDate("dia_cupom"));
	
		    if(mesAtual == data.get(Calendar.DAY_OF_MONTH))
		    {
		    	
		    	return 1;
		    }
	
		    
	        }
		rs.close();
		stmt.close();
		//connection.close();
	     return -1;
	 } catch (SQLException e) {
	         throw new RuntimeException(e);
	  }
    }

public void atualizarDiaCupom(Long id_cliente, Calendar c) {
	 int aux=0;
	  
	  String sql = "UPDATE cupom_cliente SET dia_cupom=? WHERE id_cupomCliente=? and situacao='ativo' ";
		
	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

	        java.sql.Date newDate = new java.sql.Date(c.getTimeInMillis());  
         
	       	        
	       stmt.setDate(1, newDate );	     
	       stmt.setLong(2, id_cliente); 
	     
	        // executa
	        stmt.executeUpdate();
	        System.out.print("update de mes com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }

}

public Cupom getCupomAtivo(long id_cliente) {
	try {	
		 Cupom cup = new Cupom();
	PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from cupom_cliente where id_cupomCliente='"+id_cliente+"' and situacao='ativo' ");
	ResultSet rs= stmt.executeQuery();
	
		while(rs.next()){
		   
		    cup.setProgresso(rs.getInt("progresso"));
		    cup.setId_Cupom(rs.getInt("id_cupom"));
		    cup.setId_c_cli(rs.getInt("id_cupomCliente"));
		    cup.setId_c_com(rs.getInt("id_cupomComerciante"));
		    
		    // montando a data através do Calendar
		    Calendar data = Calendar.getInstance();
		    Calendar data_dia = Calendar.getInstance();
		    data.setTime(rs.getDate("mes_cupom"));
		    cup.setMes_cup(data);
		    data_dia.setTime(rs.getDate("dia_cupom"));
		    cup.setDia_cup(data_dia);
	
		    
	        }
		rs.close();
		stmt.close();
		return cup;
		//connection.close();
	    
	 } catch (SQLException e) {
	         throw new RuntimeException(e);
	  }

    }


public void atualizarProgressoCupom(Long id_cliente, int valor) {
	 int aux=0;
	  
	  String sql = "UPDATE cupom_cliente SET progresso=? WHERE id_cupomCliente=? and situacao='ativo' ";
		
	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

        
	       	        
	       stmt.setInt(1, valor );	     
	       stmt.setLong(2, id_cliente); 
	     
	        // executa
	        stmt.executeUpdate();
	        System.out.print("update de mes com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }

}

public void atualizarUsarCupom(Long id_cliente, Long id_comerciante, int id_cupom) {
	 int aux=0;
	  
	  String sql = "UPDATE cupom_cliente SET progresso=0 , id_cupomComerciante=? WHERE id_cupom=? ";
		
	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

       
	       	        
	       stmt.setLong(1, id_comerciante );	     
	       stmt.setLong(2, id_cupom); 
	     
	        // executa
	        stmt.executeUpdate();
	        System.out.print("update de mes com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }

}

public int verificarCupomComerciante(Long id_cliente, Long id_comerciante) {
	 int aux=0;
	  
	 try {	
		
	PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from cupom_cliente where id_cupomCliente='"+id_cliente+"' and id_cupomComerciante='"+id_comerciante+"' ");
	ResultSet rs= stmt.executeQuery();
	
		while(rs.next()){
		   
		  
		    
		    // montando a data através do Calendar
		    Calendar data = Calendar.getInstance();
		    Calendar data_mes = Calendar.getInstance();

		    data_mes.setTime(rs.getDate("mes_cupom"));
		
		    
		    if(data.get(Calendar.MONTH) == data_mes.get(Calendar.MONTH)){
	
		       return 1;
		    	
	        }
		    
		rs.close();
		stmt.close();
		
		//connection.close();
	    
	 }
		
		return -1;
	 }catch (SQLException e) {
	         throw new RuntimeException(e);
	  }

	 
}

public int  getCupomNaoCompleto(long id_cliente) {
	try {	
		 int id;
	PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from cupom_cliente where id_cupomCliente='"+id_cliente+"' and situacao='inativo' ");
	ResultSet rs= stmt.executeQuery();
	
		while(rs.next()){
		   

		    id = rs.getInt("id_cupom");
	
	
		    return id;
		    
	        }
		rs.close();
		stmt.close();
		return -1;
		//connection.close();
	    
	 } catch (SQLException e) {
	         throw new RuntimeException(e);
	  }

  
}

public void atualizarSituacaoCupomAtivo(Long id_cliente) {
	 int aux=0;
	  
	  String sql = "UPDATE cupom_cliente SET situacao=? WHERE id_cupomCliente=? and situacao='ativo' ";
		
	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

        
	       	        
	       stmt.setString(1, "completo");	     
	       stmt.setLong(2, id_cliente); 
	     
	        // executa
	        stmt.executeUpdate();
	        System.out.print("update de mes com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }

}


public void atualizarCupomAtivo(Long id_cliente) {
	 int aux=0;
	  
	 int id_cupom = getCupomNaoCompleto(id_cliente);
	 
	  String sql = "UPDATE cupom_cliente SET situacao=? WHERE id_cupom=? and situacao !='completo' ";
		
	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

       
	       	        
	       stmt.setString(1, "ativo");	     
	       stmt.setLong(2, id_cupom); 
	     
	        // executa
	        stmt.executeUpdate();
	        System.out.print("update de mes com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }

}


}






